----- Create database: Medical Store Billing System -----
---------------------------------------------------------

create database MSBS
go
use MSBS
go

----- Create table ---------------
----------------------------------
----- Table: UserType ------------

create table UserType
(
	UserTypeCode varchar(15) primary key not null,
	UserTypeName varchar(10) not null
)
go
----- Table: Users ------------

create table Users
(
	ID int identity(1,1) not null,
	UserCode varchar(15) primary key not null,
	UserTypeCode varchar(15) not null,
	UserFullName varchar(100) not null,
	UserAddress varchar(200),
	UserPhone varchar(20),
	UserEmail varchar(100),
	Username varchar(100) not null,
	Pass varchar(50) not null,
	CreatedDate datetime,
	LastUpdatedDate datetime,
	CreatedBy varchar(100),
	LastUpdateBy varchar(100),
	UserActive bit
)
go
----- Table: Supplier ------------

create table Supplier
(
	ID int identity(1,1) not null,
	SupplierCode varchar(15) primary key not null,
	SupplierName varchar(100) not null,
	SupplierAddress varchar(200),
	SupplierPhone varchar(20),
	SupplierEmail varchar(100),
	SupplierWebsite varchar(100),
	SupplierFax varchar(20),
	CreatedDate datetime,
	LastUpdatedDate datetime,
	CreatedBy varchar(100),
	LastUpdatedBy varchar(100),
	SupplierStatus varchar(10)
)
go
----- Table: MedicineType ------------

create table MedicineType
(
	MedicineTypeCode varchar(15) primary key not null,
	MedicineTypeName varchar(100) not null,
	MedicineTypeDescription varchar(200) not null,
	CreatedDate datetime,
	LastUpdatedDate datetime,
	CreatedBy varchar(100),
	LastUpdatedBy varchar(100),
	MedicineTypeStatus varchar(10)
)
go
----- Table: Medicine ------------

create table Medicine
(
	ID int identity(1,1) not null,
	MedicineCode varchar(15) primary key not null,
	medicineTypeCode varchar(15) not null,
	SupplierCode varchar(15) not null,
	MedicineName varchar(100) not null,
	MeasureName varchar(50),
	ManufactureDate date,
	ExpiryDate date,
	ImportDate date,
	Origin varchar(10),
	Used varchar(200),
	UserGuide varchar(200),
	PurchasePrice float default(0),
	SellingPrice float default(0),
	PricePerUnit float default(0),
	AvailableQuantity int,
	Unit varchar(7),
	CreatedDate datetime,
	LastUpdatedDate datetime,
	CreatedBy varchar(100),
	LastUpdatedBy varchar(100),
	MedicineStatus varchar(10)
)
go
----- Table: StockStatistics ------------

create table StockStatistics
(
	ID int identity(1,1) not null,
	StockStatisticsCode varchar(15) primary key not null,
	MedicineCode varchar(15) not null,
	SupplierCode varchar(15) not null,
	AvailableQuantity int,
	CreatedDate datetime,
	LastUpdatedDate datetime,
	CreatedBy varchar(100),
	LastUpdatedBy varchar(100)
)
go

----- Table: Customer ------------

drop table Customer
(
	ID int identity(1,1) not null,
	CustomerCode varchar(15) primary key not null,
	CustomerType varchar(50) not null,
	CustomerFullName varchar(100) not null,
	CustomerRelationship varchar(15),
	CustomerAddress varchar(200),
	CustomerPhone varchar(20),
	CustomerEmail varchar(100),
	CreatedDate datetime,
	LastUpdatedDate datetime,
	CreatedBy varchar(100),
	LastUpdatedBy varchar(100),
	CustomerStatus varchar(10),
	
)
go





----- Table: PurchaseOrders ------------

create table PurchaseOrders
(
	ID int identity(1,1) not null,
	PurchaseOrdersCode varchar(15) primary key not null,
	CustomerCode varchar(15) not null,
	UserCode varchar(15) not null,
	PurchaseOrdersDate datetime not null,
	RequiredDate datetime not null,
	ShippedDate datetime not null,
	AddressToDelivery varchar(200),
	TotalAmount float default(0),
	CreatedDate datetime,
	LastUpdatedDate datetime,
	CreatedBy varchar(100),
	LastUpdatedBy varchar(100),
	OrdersStatus varchar(10)
)
go
----- Table: PurchaseOrdersDetails ------------

create table PurchaseOrdersDetails
(
	PurchaseOrdersCode varchar(15) not null,
	MedicineCode varchar(15) not null,
	Quantity int,
	AmountPrice float default(0),
	Price float default(0),
	primary key(PurchaseOrdersCode, MedicineCode)
)
go
----- Table: Bill ------------

create table Bill
(
	ID int identity(1,1) not null,
	BillCode varchar(15) primary key not null,
	UserCode varchar(15) not null,
	CustomerCode varchar(15) not null,
	BillType varchar(10) not null,
	AddressToDelivery varchar(200),
	StartDate datetime not null,
	ExpiryDate datetime not null,
	Tax float,
	TotalAmount float default(0),
	CreatedDate datetime,
	LastUpdatedDate datetime,
	CreatedBy varchar(100),
	LastUpdatedBy varchar(100),
	BillStatus varchar(10)
)
go
----- Table: BillDetails ------------

create table BillDetails
(
	BillCode varchar(15) not null,
	MedicineCode varchar(15) not null,
	Quantity int not null,
	AmountPrice float default(0) not null,
	Price float default(0) not null,
	CreatedDate datetime,
	LastUpdatedDate datetime,
	CreatedBy varchar(100),
	LastUpdatedBy varchar(100),
	primary key(BillCode, MedicineCode)
)
go

----- Alter table ----------------
----------------------------------
ALTER TABLE Users
ADD CONSTRAINT fk_Users_UserType
FOREIGN KEY(UserTypeCode) REFERENCES UserType(UserTypeCode)
GO

ALTER TABLE Bill
ADD CONSTRAINT fk_Bill_Customer
FOREIGN KEY(CustomerCode) REFERENCES Customer(CustomerCode)
GO

ALTER TABLE Bill
ADD CONSTRAINT fk_Bill_Users
FOREIGN KEY(UserCode) REFERENCES Users(UserCode)
GO

ALTER TABLE BillDetails
ADD CONSTRAINT fk_BillDetails_Bill
FOREIGN KEY(BillCode) REFERENCES Bill(BillCode)
GO

ALTER TABLE BillDetails
ADD CONSTRAINT fk_BillDetails_Medicine
FOREIGN KEY(MedicineCode) REFERENCES Medicine(MedicineCode)
GO

ALTER TABLE PurchaseOrders
ADD CONSTRAINT fk_PurchaseOrders_Customer
FOREIGN KEY(CustomerCode) REFERENCES Customer(CustomerCode)
GO

ALTER TABLE PurchaseOrders
ADD CONSTRAINT fk_PurchaseOrders_Users
FOREIGN KEY(UserCode) REFERENCES Users(UserCode)
GO

ALTER TABLE PurchaseOrdersDetails
ADD CONSTRAINT fk_PurchaseOrdersDetails_Medicine
FOREIGN KEY(MedicineCode) REFERENCES Medicine(MedicineCode)
GO

ALTER TABLE PurchaseOrdersDetails
ADD CONSTRAINT fk_PurchaseOrdersDetails_PurchaseOrders
FOREIGN KEY(PurchaseOrdersCode) REFERENCES PurchaseOrders(PurchaseOrdersCode)
GO

ALTER TABLE Medicine
ADD CONSTRAINT fk_Medicine_MedicineType
FOREIGN KEY(MedicineTypeCode) REFERENCES MedicineType(MedicineTypeCode)
GO

ALTER TABLE Medicine
ADD CONSTRAINT fk_Medicine_Supplier
FOREIGN KEY(SupplierCode) REFERENCES Supplier(SupplierCode)
GO

ALTER TABLE StockStatistics
ADD CONSTRAINT fk_StockStatistics_Medicine
FOREIGN KEY(MedicineCode) REFERENCES Medicine(MedicineCode)
GO

ALTER TABLE StockStatistics
ADD CONSTRAINT FK_StockStatistics_Supplier
FOREIGN KEY(SupplierCode) REFERENCES Supplier(SupplierCode)
GO